package com.sinodata.ns.cell;

import com.sinodata.ns.sv.ISupervisor;

import jt.classic.system.ISystem;
import jt.classic.system.SimpleAction;
import jt.classic.system.database.SystemDataSource;
import jt.classic.system.log.LogManager;
import jt.classic.system.object.ObjectManager;
import jt.classic.system.purview.PurviewManager;
import jt.classic.system.user.IUser;

import net.sf.json.JSONArray;

import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import org.limp.basework.impl.CommonBean4HSQ;

import org.limp.mine.DataTrimmerI;
import org.limp.mine.DataTrimmerI.DataItemList;
import org.limp.mine.Label;
import org.limp.mine.QueryJack;
import org.limp.mine.QueryJackFactory;
import org.limp.mine.StringTool;

import java.sql.Connection;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


public class CellAction extends SimpleAction {
    /**
        * 监理状态
        * @param mapping
        * @param form
        * @param request
        * @param response
        * @return ActionForward
        * @throws Exception
        */
    public ActionForward svstatus(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        Connection conn = null;

        CommonBean4HSQ req = new CommonBean4HSQ(request);

        Map param = req.getResource();

        String bdid = StringTool.checkString(param.get("bdid"));

        try {
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            CellManager cm = new CellManager(conn);

            ISupervisor sv = cm.getSupervisorByBDId(bdid);

            request.setAttribute("sv", sv);
        } catch (Exception ex) {
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return mapping.findForward("svstatus");
    }

    /**
    * 查询列表
    * @param mapping
    * @param form
    * @param request
    * @param response
    * @return
    * @throws Exception
    */
    public ActionForward list(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        Connection conn = null;

        CommonBean4HSQ req = new CommonBean4HSQ(request);

        Map param = req.getResource();

        String pageNO = StringTool.checkString(param.get("pageNO"));

        String start = StringTool.checkString(param.get("start"), "0");

        String size = StringTool.checkString(param.get("limit"), "20");

        pageNO = String.valueOf((Integer.parseInt(start) / Integer.parseInt(
                    size)) + 1);

        StringBuffer where = new StringBuffer(" WHERE 1 = 1 ");

        String cname = StringTool.checkString(param.get("cname"));

        if (!cname.equals("")) {
            where.append(" AND C_NAME LIKE '%" + cname + "%' ");
        }

        String cunit = StringTool.checkString(param.get("cunit"));

        if (!cunit.equals("")) {
            where.append(" AND C_UNIT LIKE '%" + cunit + "%' ");
        }

        String ctype = StringTool.checkString(param.get("ctype"));

        if (!ctype.equals("")) {
            where.append(" AND C_TYPE LIKE '%" + ctype + "%' ");
        }

        String carea = StringTool.checkString(param.get("carea"));

        if (!carea.equals("")) {
            where.append(" AND C_AREA LIKE '%" + carea + "%' ");
        }

        String sql1 = "SELECT COUNT(*) FROM APP_NS_CELL" + where.toString();

        String sql2 = "SELECT * FROM APP_NS_CELL " + where.toString() +
            " ORDER BY C_TIME DESC";

        String url = jt.classic.system.ISystem.getContextPath() +
            "/app/cellaction.do?method=list";

        try {
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            QueryJack plus = QueryJackFactory.getInstance().createQueryJack(false);

            plus.setParameter(QueryJackFactory.PAGINATION_SQL1, sql1);
            plus.setParameter(QueryJackFactory.PAGINATION_SQL2, sql2);
            plus.setParameter(QueryJackFactory.DATABASE_CONNECTION, conn);
            plus.setParameter(QueryJackFactory.PAGINATION_LINKSTRING, url);
            plus.setParameter(QueryJackFactory.PAGINATION_COUNT, size);
            plus.setParameter(QueryJackFactory.PAGINATION_PAGENO, pageNO);

            // plus.setParameter(QueryJackFactory.PAGINATION_UPDATEPAGENO, "");
            List resultList = plus.work();

            if ((resultList != null) && !resultList.isEmpty()) {
                CellManager cm = new CellManager(conn);

                for (int i = 0; i < resultList.size(); i++) {
                    Map info = (Map) resultList.get(i);

                    String id = (String) info.get("C_ID");

                    List list = cm.getCellBD(id);

                    info.put("CELLBDLIST", list);
                }
            }

            Label label = plus.getLabel();

            JSONArray jsonArray = JSONArray.fromObject(resultList);

            String jsonString = "{\"pageNO\":\"" + label.current() +
                "\",\"totalPage\":\"" + label.totalfan() +
                "\",\"totalCount\":\"" + label.totaldata() +
                "\",\"queryList\":";
            jsonString += jsonArray.toString();
            jsonString += "}";

            request.setAttribute("result", jsonString);
        } catch (Exception ex) {
            request.setAttribute("result", "{success: false}");
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return mapping.findForward("jsonresult");
    }

    /**
     * 监理设置(选择)列表
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward sellist(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        Connection conn = null;

        IUser user = ISystem.getSessionUser(request);

        CommonBean4HSQ req = new CommonBean4HSQ(request);

        Map param = req.getResource();

        String userId = StringTool.checkString(param.get("userid"));

        String pageNO = StringTool.checkString(param.get("pageNO"));

        String start = StringTool.checkString(param.get("start"), "0");

        String size = StringTool.checkString(param.get("limit"), "20");

        pageNO = String.valueOf((Integer.parseInt(start) / Integer.parseInt(
                    size)) + 1);

        String purviewSQL = ObjectManager.getObjectPurviewSQL("cell", user, 0,
                null);

        String whereSQL = " 1 = 1 "; //" APP_NS_CELLBD.CB_ID NOT IN (SELECT NU_BDID FROM APP_NS_JLBD GROUP BY NU_BDID) ";

        String url = jt.classic.system.ISystem.getContextPath() +
            "/app/cellaction.do?method=sellist";

        try {
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            /*SupervisorManager sm = new SupervisorManager(conn);

            if (!userId.equals("")) {
                ISupervisor sv = sm.getSupervisorByUserId(userId);

                if (sv.ibd() != null) {
                    whereSQL += (" AND APP_NS_CELLBD.CB_ID <> " +
                    sv.ibd().id());
                }
            }*/
            String sql1 =
                "SELECT COUNT(APP_NS_CELL.C_ID) FROM APP_NS_CELL,(SELECT APP_NS_CELLBD.C_ID FROM APP_NS_CELLBD, (" +
                purviewSQL + ") A WHERE " + whereSQL +
                " AND APP_NS_CELLBD.CB_ID = A.OBJ_ID GROUP BY APP_NS_CELLBD.C_ID) A WHERE APP_NS_CELL.C_ID = A.C_ID";

            String sql2 =
                "SELECT APP_NS_CELL.* FROM APP_NS_CELL,(SELECT APP_NS_CELLBD.C_ID FROM APP_NS_CELLBD, (" +
                purviewSQL + ") A WHERE " + whereSQL +
                " AND APP_NS_CELLBD.CB_ID = A.OBJ_ID GROUP BY APP_NS_CELLBD.C_ID) A WHERE APP_NS_CELL.C_ID = A.C_ID ORDER BY APP_NS_CELL.C_TIME";

            QueryJack plus = QueryJackFactory.getInstance().createQueryJack(false);

            plus.setParameter(QueryJackFactory.PAGINATION_SQL1, sql1);
            plus.setParameter(QueryJackFactory.PAGINATION_SQL2, sql2);
            plus.setParameter(QueryJackFactory.DATABASE_CONNECTION, conn);
            plus.setParameter(QueryJackFactory.PAGINATION_LINKSTRING, url);
            plus.setParameter(QueryJackFactory.PAGINATION_COUNT, size);
            plus.setParameter(QueryJackFactory.PAGINATION_PAGENO, pageNO);

            // plus.setParameter(QueryJackFactory.PAGINATION_UPDATEPAGENO, "");
            List resultList = plus.work();

            if ((resultList != null) && !resultList.isEmpty()) {
                CellManager cm = new CellManager(conn);

                for (int i = 0; i < resultList.size(); i++) {
                    Map info = (Map) resultList.get(i);

                    String id = (String) info.get("C_ID");

                    CellInfo ci = cm.getCellById(id, user);

                    List list = ci.ibd();

                    if ((list != null) && !list.isEmpty()) {
                        StringBuffer bdids = new StringBuffer();
                        StringBuffer bdnames = new StringBuffer();

                        for (int j = 0; j < list.size(); j++) {
                            CellBDInfo cbi = (CellBDInfo) list.get(j);

                            if (j < (list.size() - 1)) {
                                bdids.append(cbi.id() + ",");
                                bdnames.append(cbi.iname() + ",");
                            } else {
                                bdids.append(cbi.id());
                                bdnames.append(cbi.iname());
                            }
                        }

                        info.put("CB_ID", bdids.toString());
                        info.put("CB_NAME", bdnames.toString());
                    }
                }
            }

            Label label = plus.getLabel();

            JSONArray jsonArray = JSONArray.fromObject(resultList);

            String jsonString = "{\"pageNO\":\"" + label.current() +
                "\",\"totalPage\":\"" + label.totalfan() +
                "\",\"totalCount\":\"" + label.totaldata() +
                "\",\"queryList\":";
            jsonString += jsonArray.toString();
            jsonString += "}";

            request.setAttribute("result", jsonString);
        } catch (Exception ex) {
            request.setAttribute("result", "{success: false}");
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return mapping.findForward("jsonresult");
    }

    /**
     * 查询(选择)列表
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward querysellist(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        Connection conn = null;

        IUser user = ISystem.getSessionUser(request);

        CommonBean4HSQ req = new CommonBean4HSQ(request);

        Map param = req.getResource();

        String pageNO = StringTool.checkString(param.get("pageNO"));

        String start = StringTool.checkString(param.get("start"), "0");

        String size = StringTool.checkString(param.get("limit"), "20");

        pageNO = String.valueOf((Integer.parseInt(start) / Integer.parseInt(
                    size)) + 1);

        String purviewSQL = ObjectManager.getObjectPurviewSQL("cell", user, 0,
                null);

        String sql1 =
            "SELECT COUNT(APP_NS_CELL.C_ID) FROM APP_NS_CELL,(SELECT APP_NS_CELLBD.C_ID FROM APP_NS_CELLBD, (" +
            purviewSQL +
            ") A WHERE APP_NS_CELLBD.CB_ID = A.OBJ_ID GROUP BY APP_NS_CELLBD.C_ID) A WHERE APP_NS_CELL.C_ID = A.C_ID";

        String sql2 =
            "SELECT APP_NS_CELL.* FROM APP_NS_CELL,(SELECT APP_NS_CELLBD.C_ID FROM APP_NS_CELLBD, (" +
            purviewSQL +
            ") A WHERE APP_NS_CELLBD.CB_ID = A.OBJ_ID GROUP BY APP_NS_CELLBD.C_ID) A WHERE APP_NS_CELL.C_ID = A.C_ID ORDER BY APP_NS_CELL.C_TIME";

        String url = jt.classic.system.ISystem.getContextPath() +
            "/app/cellaction.do?method=querysellist";

        try {
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            QueryJack plus = QueryJackFactory.getInstance().createQueryJack(false);

            plus.setParameter(QueryJackFactory.PAGINATION_SQL1, sql1);
            plus.setParameter(QueryJackFactory.PAGINATION_SQL2, sql2);
            plus.setParameter(QueryJackFactory.DATABASE_CONNECTION, conn);
            plus.setParameter(QueryJackFactory.PAGINATION_LINKSTRING, url);
            plus.setParameter(QueryJackFactory.PAGINATION_COUNT, size);
            plus.setParameter(QueryJackFactory.PAGINATION_PAGENO, pageNO);

            // plus.setParameter(QueryJackFactory.PAGINATION_UPDATEPAGENO, "");
            List resultList = plus.work();

            if ((resultList != null) && !resultList.isEmpty()) {
                CellManager cm = new CellManager(conn);

                for (int i = 0; i < resultList.size(); i++) {
                    Map info = (Map) resultList.get(i);

                    String id = (String) info.get("C_ID");

                    CellInfo ci = cm.getCellById(id, user);

                    List list = ci.ibd();

                    if ((list != null) && !list.isEmpty()) {
                        StringBuffer bdids = new StringBuffer();
                        StringBuffer bdnames = new StringBuffer();

                        for (int j = 0; j < list.size(); j++) {
                            CellBDInfo cbi = (CellBDInfo) list.get(j);

                            if (j < (list.size() - 1)) {
                                bdids.append(cbi.id() + ",");
                                bdnames.append(cbi.iname() + ",");
                            } else {
                                bdids.append(cbi.id());
                                bdnames.append(cbi.iname());
                            }
                        }

                        info.put("CB_ID", bdids.toString());
                        info.put("CB_NAME", bdnames.toString());
                    }
                }
            }

            Label label = plus.getLabel();

            JSONArray jsonArray = JSONArray.fromObject(resultList);

            String jsonString = "{\"pageNO\":\"" + label.current() +
                "\",\"totalPage\":\"" + label.totalfan() +
                "\",\"totalCount\":\"" + label.totaldata() +
                "\",\"queryList\":";
            jsonString += jsonArray.toString();
            jsonString += "}";

            request.setAttribute("result", jsonString);
        } catch (Exception ex) {
            request.setAttribute("result", "{success: false}");
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return mapping.findForward("jsonresult");
    }

    /**
     * (所有的)查询列表
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward alllist(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        Connection conn = null;

        IUser user = ISystem.getSessionUser(request);

        CommonBean4HSQ req = new CommonBean4HSQ(request);

        Map param = req.getResource();

        String pageNO = StringTool.checkString(param.get("pageNO"));

        String start = StringTool.checkString(param.get("start"), "0");

        String size = StringTool.checkString(param.get("limit"), "20");

        pageNO = String.valueOf((Integer.parseInt(start) / Integer.parseInt(
                    size)) + 1);

        String sql1 = "SELECT COUNT(APP_NS_CELL.C_ID) FROM APP_NS_CELL";

        String sql2 = "SELECT APP_NS_CELL.* FROM APP_NS_CELL ORDER BY APP_NS_CELL.C_TIME";

        String url = jt.classic.system.ISystem.getContextPath() +
            "/app/cellaction.do?method=querysellist";

        try {
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            QueryJack plus = QueryJackFactory.getInstance().createQueryJack(false);

            plus.setParameter(QueryJackFactory.PAGINATION_SQL1, sql1);
            plus.setParameter(QueryJackFactory.PAGINATION_SQL2, sql2);
            plus.setParameter(QueryJackFactory.DATABASE_CONNECTION, conn);
            plus.setParameter(QueryJackFactory.PAGINATION_LINKSTRING, url);
            plus.setParameter(QueryJackFactory.PAGINATION_COUNT, size);
            plus.setParameter(QueryJackFactory.PAGINATION_PAGENO, pageNO);

            // plus.setParameter(QueryJackFactory.PAGINATION_UPDATEPAGENO, "");
            List resultList = plus.work();

            if ((resultList != null) && !resultList.isEmpty()) {
                CellManager cm = new CellManager(conn);

                for (int i = 0; i < resultList.size(); i++) {
                    Map info = (Map) resultList.get(i);

                    String id = (String) info.get("C_ID");

                    List list = cm.getCellBD(id);

                    if ((list != null) && !list.isEmpty()) {
                        StringBuffer bdids = new StringBuffer();
                        StringBuffer bdnames = new StringBuffer();

                        for (int j = 0; j < list.size(); j++) {
                            Map cbi = (Map) list.get(j);

                            if (j < (list.size() - 1)) {
                                bdids.append(cbi.get("CB_ID") + ",");
                                bdnames.append(cbi.get("CB_NAME") + ",");
                            } else {
                                bdids.append(cbi.get("CB_ID"));
                                bdnames.append(cbi.get("CB_NAME"));
                            }
                        }

                        info.put("CB_ID", bdids.toString());
                        info.put("CB_NAME", bdnames.toString());
                    }
                }
            }

            Label label = plus.getLabel();

            JSONArray jsonArray = JSONArray.fromObject(resultList);

            String jsonString = "{\"pageNO\":\"" + label.current() +
                "\",\"totalPage\":\"" + label.totalfan() +
                "\",\"totalCount\":\"" + label.totaldata() +
                "\",\"queryList\":";
            jsonString += jsonArray.toString();
            jsonString += "}";

            request.setAttribute("result", jsonString);
        } catch (Exception ex) {
            request.setAttribute("result", "{success: false}");
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return mapping.findForward("jsonresult");
    }

    /**
     * 删除设计单元
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return ActionForward
     * @throws Exception
     */
    public ActionForward delete(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        Connection conn = null;

        IUser user = ISystem.getSessionUser(request);

        CommonBean4HSQ req = new CommonBean4HSQ(request);

        Map param = req.getResource();

        String id = (String) param.get("id");

        try {
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            DataTrimmerI trimmer = new DataTrimmerI(conn);

            CellManager cm = new CellManager(conn);

            CellInfo cellInfo = cm.getCellById(id);

            StringBuffer sql = new StringBuffer();
            sql.append(
                "UPDATE APP_NS_JLRY SET NU_BDID = ?, NU_BD = ? WHERE NU_BDID = ?;");
            sql.append("DELETE FROM APP_NS_CELLBD WHERE C_ID = ?;");
            sql.append("DELETE FROM APP_NS_CELL WHERE C_ID = ?");
            sql.append(
                "DELETE FROM APP_NS_JLBD WHERE NU_ID IN (SELECT NU_ID FROM APP_NS_JLRY WHERE NU_BDID = ?) A;");

            List dilList = new ArrayList();

            DataItemList dil0 = trimmer.createDIL();
            dil0.set("0");
            dil0.set("");
            dil0.set(id);

            DataItemList dil1 = trimmer.createDIL();
            dil1.set(id);

            DataItemList dil2 = trimmer.createDIL();
            dil2.set(id);

            DataItemList dil3 = trimmer.createDIL();
            dil3.set(id);

            dilList.add(dil0);
            dilList.add(dil1);
            dilList.add(dil2);
            dilList.add(dil3);

            if (trimmer.executeBatch(sql.toString(), dilList)) {
                List bdList = cellInfo.ibd();

                if ((bdList != null) && !bdList.isEmpty()) {
                    PurviewManager pm = new PurviewManager(conn);

                    for (int i = 0; i < bdList.size(); i++) {
                        CellBDInfo cbinfo = (CellBDInfo) bdList.get(i);
                        pm.removePurview(cbinfo.id(), "cell");
                    }
                }
            }

            LogManager lm = new LogManager(conn);

            lm.log(user, request.getRemoteAddr(), "", "", "removeCell",
                "delete", cellInfo.getResource().toString());

            conn.commit();

            request.setAttribute("result", "{success: true}");
        } catch (Exception ex) {
            conn.rollback();
            request.setAttribute("result", "{success: false}");
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return mapping.findForward("result");
    }

    /**
     * 删除标段
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return ActionForward
     * @throws Exception
     */
    public ActionForward remove(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        Connection conn = null;

        IUser user = ISystem.getSessionUser(request);

        CommonBean4HSQ req = new CommonBean4HSQ(request);

        Map param = req.getResource();

        String id = (String) param.get("id");

        try {
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            CellManager cm = new CellManager(conn);

            CellInfo cellInfo = cm.getCellByBDId(id);

            DataTrimmerI trimmer = new DataTrimmerI(conn);

            StringBuffer sql = new StringBuffer(
                    "DELETE FROM APP_NS_CELLBD WHERE CB_ID = ?;");
            sql.append("DELETE FROM APP_NS_JLBD WHERE NU_BDID = ?;");

            List dilList = new ArrayList();

            DataItemList dil0 = trimmer.createDIL();
            dil0.set(id);

            DataItemList dil1 = trimmer.createDIL();
            dil1.set(id);

            dilList.add(dil0);
            dilList.add(dil1);

            if (trimmer.executeBatch(sql.toString(), dilList)) {
                PurviewManager pm = new PurviewManager(conn);
                pm.removePurview(id, "cell");
            }

            LogManager lm = new LogManager(conn);

            lm.log(user, request.getRemoteAddr(), "", "", "removeCellBD",
                "delete", cellInfo.ibd(id).getResource().toString());

            conn.commit();

            request.setAttribute("result", "{success: true}");
        } catch (Exception ex) {
            conn.rollback();
            request.setAttribute("result", "{success: false}");
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return mapping.findForward("result");
    }

    /**
     * 获取授权访问的设计单元
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return ActionForward
     * @throws Exception
     */
    public ActionForward getMyCell(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        Connection conn = null;

        IUser user = ISystem.getSessionUser(request);

        try {
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            CellManager cm = new CellManager(conn);

            List list = cm.getCellByUser(user);

            if ((list != null) && !list.isEmpty()) {
                JLBDManager jm = new JLBDManager(conn);

                for (int i = 0; i < list.size(); i++) {
                    CellInfo cell = (CellInfo) list.get(i);

                    List jdList = jm.getJLBDInfoByCellId(cell.id());

                    cell.getResource().put("jdList", jdList);
                }
            }

            JSONArray jsonArray = JSONArray.fromObject(list);

            String jsonString = "{success: true, queryList :";
            jsonString += jsonArray.toString();
            jsonString += "}";

            request.setAttribute("result", jsonString);
        } catch (Exception ex) {
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return mapping.findForward("jsonresult");
    }
}
